Experienced / Expert level questions

Ques 1. Write a SQL query to find the second highest salary from an Employee table.

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

Ques 2. Write a SQL query to find the third highest salary from an Employee table.

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee));

Ques 3. Write a SQL query to find the nth highest salary from an Employee table.

SELECT salary FROM Employee ORDER BY salary DESC LIMIT 1 OFFSET n-1;

Ques 4. Write a SQL query to find the average salary of employees in each department, excluding departments with fewer than five employees.

SELECT department, AVG(salary) FROM Employee GROUP BY department HAVING COUNT(*) >= 5;

Ques 5. Explain the concept of SQL injection.

SQL injection is a security vulnerability where an attacker can insert malicious SQL code into a query, potentially gaining unauthorized access to a database.

Ques 6. What is the purpose of the SQL TRIGGER?

A TRIGGER is a set of instructions that are automatically executed (or 'triggered') in response to specific events, such as INSERTs, UPDATEs, or DELETEs, on a particular table.

Example:

CREATE TRIGGER trigger_name BEFORE INSERT ON table FOR EACH ROW BEGIN -- trigger logic END;

Ques 7. Explain the purpose of the SQL ROLLUP operator.

The ROLLUP operator is used in conjunction with the GROUP BY clause to generate subtotals and grand totals for a set of columns in the result set.

Example:

SELECT department, city, SUM(salary) FROM Employee GROUP BY ROLLUP (department, city);

Ques 8. Write a SQL query to find the nth highest salary without using the LIMIT clause.

SELECT salary FROM Employee e1 WHERE n-1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e1.salary <= e2.salary);

Ques 9. Explain the purpose of the SQL LEAD() and LAG() functions.

The LEAD() function is used to access the next row's data in the result set, while the LAG() function is used to access the previous row's data.

Example:

SELECT employee_name, salary, LEAD(salary) OVER (ORDER BY salary) AS NextSalary FROM Employee;

Ques 10. What is the purpose of the SQL CROSS APPLY operator?

The CROSS APPLY operator is used to invoke a table-valued function for each row returned by the outer query. It is similar to the INNER JOIN clause.

Example:

SELECT * FROM table1 CROSS APPLY function(table1.column) AS alias;

Ques 11. Write a SQL query to find the employees who have the highest salary in each department.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 1;

Ques 12. Explain the purpose of the SQL MERGE statement.

The MERGE statement is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table. It is also known as an 'upsert' operation.

Example:

MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT (column1, column2) VALUES (value1, value2);

Ques 13. What is the purpose of the SQL WINDOW functions?

WINDOW functions perform a calculation across a set of table rows related to the current row. They are used with the OVER() clause to define a window or a subset of rows for the calculation.

Example:

SELECT employee_name, salary, AVG(salary) OVER (PARTITION BY department) AS AvgSalary FROM Employee;

Ques 14. Write a SQL query to find the employees with the second-highest salary in each department.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 2;

Ques 15. Explain the purpose of the SQL XML functions (XMLAGG, XMLPARSE, XMLQUERY, etc.).

XML functions in SQL are used to process XML data. XMLAGG is used to concatenate XML values, XMLPARSE is used to convert a string to XML, and XMLQUERY is used to extract data from XML.

Ques 16. What is the purpose of the SQL PIVOT and UNPIVOT operators?

The PIVOT operator is used to rotate rows into columns, while the UNPIVOT operator is used to rotate columns into rows.

Example:

SELECT * FROM (SELECT department, salary FROM Employee) AS SourceTable PIVOT (SUM(salary) FOR department IN ([Dept1], [Dept2], [Dept3])) AS PivotTable;

Ques 17. Write a SQL query to find the cumulative sum of a column in a result set.

SELECT column, SUM(column) OVER (ORDER BY some_order_column) AS CumulativeSum FROM table;

Ques 18. Explain the purpose of the SQL ROW and RANGE clauses in window functions.

ROW and RANGE are used in the OVER() clause of window functions to define the window or subset of rows for the calculation. ROW refers to a physical count, while RANGE considers values within a specified range.

Ques 19. Write a SQL query to find the employees with the highest salary in each department using the DENSE_RANK() function.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 1;

Ques 20. What is the purpose of the SQL TRY...CATCH statement?

The TRY...CATCH statement is used to handle errors in a SQL Server stored procedure. It allows for structured error handling and graceful error recovery.

Example:

BEGIN TRY -- SQL statements END TRY BEGIN CATCH -- Handling of errors END CATCH;